package com.travel.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.travel.dao.ScenicOrderDao;
import com.travel.entity.OrderStatus;
import com.travel.entity.Scenic;
import com.travel.entity.ScenicOrder;
import com.travel.entity.User;
import com.travel.util.DBUtils;
import com.travel.util.PageInfo;

public class ScenicOrderDaoImpl implements ScenicOrderDao{

	private DBUtils db = new DBUtils();
	
	@Override
	public PageInfo<ScenicOrder> findByPage(int pageNum, int pageSize, String userName) {
		StringBuffer sb = new StringBuffer();
		sb.append("select")
		  .append(" O.scenic_order_id,")
		  .append(" O.scenic_order_price,")
		  .append(" O.scenic_order_datetime,")
		  .append(" T.status_id,")
		  .append(" T.status_name,")
		  .append(" U.user_id,")
		  .append(" U.user_name,")
		  .append(" S.scenic_id,")
		  .append(" S.scenic_name")
		  .append(" from tab_scenic_order O")
		  .append(" inner join tab_order_status T")
		  .append(" on O.status_id = T.status_id")
		  .append(" inner join tab_user U")
		  .append(" on O.user_id = U.user_id")
		  .append(" inner join tab_scenic S")
		  .append(" on O.scenic_id = S.scenic_id")
		  .append(" where U.user_name like concat('%',?,'%')")
		  .append(" limit ?,?");
		
		List<ScenicOrder> list = new ArrayList<ScenicOrder>();
		
		ResultSet rs = db.query(sb.toString(),userName,(pageNum-1)*pageSize, pageSize);
		System.out.println(sb.toString());
		try {
			while (rs.next()) {
				int scenicOrderId1 = rs.getInt("scenic_order_id");
				String scenicOrderPrice = rs.getString("scenic_order_price");
				String scenicOrderDatetime = rs.getString("scenic_order_datetime");
				Integer statusId = rs.getInt("status_id");
				String statusName = rs.getString("status_name");
				Integer userId = rs.getInt("user_id");
				String userName1 = rs.getString("user_name");
				Integer scenicId = rs.getInt("scenic_id");
				String scenicName = rs.getString("scenic_name");
				ScenicOrder scenicOrder= new ScenicOrder(scenicOrderId1, new BigDecimal(scenicOrderPrice), scenicOrderDatetime, new OrderStatus(statusId, statusName), new User(userId, userName1, null, null, null, null, null, null), new Scenic(scenicId, scenicName, null, null, null));
				
				list.add(scenicOrder);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		// 2.获取总记录数(未分页之前)
			int total = 0;
			sb = new StringBuffer();
			sb.append("select")
			  .append(" count(*)")
			  .append(" from tab_scenic_order O")
			  .append(" inner join tab_order_status T")
			  .append(" on O.status_id = T.status_id")
			  .append(" inner join tab_user U")
			  .append(" on O.user_id = U.user_id")
			  .append(" inner join tab_scenic S")
			  .append(" on O.scenic_id = S.scenic_id")
			  .append(" where U.user_name like concat('%',?,'%')");
			
			rs  = db.query(sb.toString(),userName);
			// 遍历结果集
			try {
				while (rs.next()) {
					total  = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			// 构建分页对象
			PageInfo<ScenicOrder> pageInfo = new PageInfo<ScenicOrder>(list, pageNum, pageSize, total);
			
			return pageInfo;
		}

	@Override
	public int scenicOrderId(int scenicOrderId) {
		String sql = "delete from tab_scenic_order where scenic_order_id=?";
		int delete = db.update(sql, scenicOrderId);
		return delete;
	}

	@Override
	public int insert(ScenicOrder scenicOrder) {
		String sql="insert into tab_scenic_order(scenic_order_id,user_id,scenic_id,scenic_order_price,scenic_order_datetime,status_id) values(?,?,?,?,?,?)";
		int insert = db.update(sql, scenicOrder.getScenicOrderId(),scenicOrder.getUser().getUserId(),scenicOrder.getScenic().getScenicId(),scenicOrder.getScenicOrderPrice(),scenicOrder.getScenicOrderDatetime(),scenicOrder.getOrderStatus().getStatusName());
		return insert;
	}

	@Override
	public int update(ScenicOrder scenicOrder) {
		
		return 0;
	}

	@Override
	public List<ScenicOrder> find() {
		StringBuffer sb = new StringBuffer();
		sb.append("select")
		  .append(" O.scenic_order_id,")
		  .append(" O.scenic_order_price,")
		  .append(" O.scenic_order_datetime,")
		  .append(" T.status_id,")
		  .append(" T.status_name,")
		  .append(" U.user_id,")
		  .append(" U.user_name,")
		  .append(" S.scenic_id,")
		  .append(" S.scenic_name")
		  .append(" from tab_scenic_order O")
		  .append(" inner join tab_order_status T")
		  .append(" on O.status_id = T.status_id")
		  .append(" inner join tab_user U")
		  .append(" on O.user_id = U.user_id")
		  .append(" inner join tab_scenic S")
		  .append(" on O.scenic_id = S.scenic_id");
		
		List<ScenicOrder> list = new ArrayList<ScenicOrder>();
		
		ResultSet rs = db.query(sb.toString());
		try {
			while (rs.next()) {
				int scenicOrderId1 = rs.getInt("scenic_order_id");
				String scenicOrderPrice = rs.getString("scenic_order_price");
				new BigDecimal(scenicOrderPrice);
				String scenicOrderDatetime = rs.getString("scenic_order_datetime");
				Integer statusId = rs.getInt("status_id");
				String statusName = rs.getString("status_name");
				Integer userId = rs.getInt("user_id");
				String userName1 = rs.getString("user_name");
				Integer scenicId = rs.getInt("scenic_id");
				String scenicName = rs.getString("scenic_name");
				ScenicOrder scenicOrder= new ScenicOrder(scenicOrderId1, new BigDecimal(scenicOrderPrice), scenicOrderDatetime, new OrderStatus(statusId, statusName), new User(userId, userName1, null, null, null, null, null, null), new Scenic(scenicId, scenicName, null, null, null));
				
				list.add(scenicOrder);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

}
